﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 27 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_Dorms] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN
	-- Create Dorm Parent relationships (both parents)
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT * FROM ((SELECT '{"ShortRelationshipName":"DP_HOUSING"'
			+ ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
			+ ',"Entity2ID":"' + CAST(RH.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN CYT.School_Term = 1 THEN ',"StartDate":"' 
					+ '08/10/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(OLDRVAYEAR AS nvarchar) + '"'
				ELSE CASE WHEN CYT.School_Term = 2 THEN ',"StartDate":"' 
					+ '12/16/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				ELSE ',"StartDate":"' 
					+ '04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				END END
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT *
	FROM (SELECT *
			FROM OpenQUERY(STUDData97,'SELECT ParentID,SpouseID,H.* FROM [Parents] 
				INNER JOIN [Housing] H ON H.DormParentID = [Parents].ParentID') SS) PRH
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {HousingDIV.Dorm}''') AS RH
		ON RH.DormName = PRH.Housing
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
		ON P.PersonID = CAST(PRH.ParentID+10000 AS NVARCHAR)
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
		ON 1>0
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {DP_Housing}''') AS DPH
		ON (P.EntityID = DPH.Entity1ID)
			AND DPH.StartDate = CASE WHEN CYT.School_Term = 1 THEN CAST('08/10/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('12/16/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
												  ELSE CAST('04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
			 AND DPH.EndDate = CASE WHEN CYT.School_Term = 1 THEN CAST('12/15/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
												  ELSE CAST('07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
	WHERE DPH.Entity2ID IS NULL)
		UNION
	(SELECT '{"ShortRelationshipName":"DP_HOUSING"'
			+ ',"Entity1ID":"' + CAST(P2.EntityID AS NVARCHAR) + '"'
			+ ',"Entity2ID":"' + CAST(RH.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN CYT.School_Term = 1 THEN ',"StartDate":"' 
					+ '08/10/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(OLDRVAYEAR AS nvarchar) + '"'
				ELSE CASE WHEN CYT.School_Term = 2 THEN ',"StartDate":"' 
					+ '12/16/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				ELSE ',"StartDate":"' 
					+ '04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				END END
			+'}' AS JSONvalue,P2.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT *
	FROM (SELECT *
			FROM OpenQUERY(STUDData97,'SELECT ParentID,SpouseID,H.* FROM [Parents] 
				INNER JOIN [Housing] H ON H.DormParentID = [Parents].ParentID') SS) PRH
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {HousingDIV.Dorm}''') AS RH
		ON RH.DormName = PRH.Housing
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P2
		ON P2.PersonID = CAST(PRH.SpouseID+10000 AS NVARCHAR) AND PRH.SpouseID <> 0
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
		ON 1>0
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {DP_Housing}''') AS DPH
		ON (P2.EntityID = DPH.Entity1ID)
			AND DPH.StartDate = CASE WHEN CYT.School_Term = 1 THEN CAST('08/10/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('12/16/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
												  ELSE CAST('04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
			 AND DPH.EndDate = CASE WHEN CYT.School_Term = 1 THEN CAST('12/15/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
												  ELSE CAST('07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
	WHERE DPH.Entity2ID IS NULL)) A

	-- Remove invalid dorm parent relationships
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True","ShortRelationshipName":"DP_HOUSING"'
			+ ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
			+ ',"Entity2ID":"' + CAST(RH.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN CYT.School_Term = 1 THEN ',"StartDate":"' 
					+ '08/10/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(OLDRVAYEAR AS nvarchar) + '"'
				ELSE CASE WHEN CYT.School_Term = 2 THEN ',"StartDate":"' 
					+ '12/16/' + CAST(OLDRVAYEAR AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				ELSE ',"StartDate":"' 
					+ '04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) + '"'
				END END
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT *
	FROM OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
		ON 1>0
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {DP_Housing}''') AS DPH
		ON (P.EntityID = DPH.Entity1ID)
			AND DPH.StartDate = CASE WHEN CYT.School_Term = 1 THEN CAST('08/10/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('12/16/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
												  ELSE CAST('04/11/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
			 AND DPH.EndDate = CASE WHEN CYT.School_Term = 1 THEN CAST('12/15/' + CAST(OLDRVAYEAR AS nvarchar) AS DateTime)
						ELSE CASE WHEN CYT.School_Term = 2 THEN CAST('04/10/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
												  ELSE CAST('07/25/' + CAST(OLDRVAYEAR+1 AS nvarchar) AS DateTime)
						END END
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {HousingDIV.Dorm}''') AS RH
		ON RH.EntityID = DPH.Entity2ID
	LEFT JOIN 
		(SELECT *
				FROM OpenQUERY(STUDData97,'SELECT ParentID,SpouseID,H.* FROM [Parents] 
					INNER JOIN [Housing] H ON H.DormParentID = [Parents].ParentID') SS) PRH
		ON RH.DormName = PRH.Housing AND (CAST(PRH.ParentID+10000 AS NVARCHAR) = P.PersonID OR 
										 (CAST(PRH.SpouseID+10000 AS NVARCHAR) = P.PersonID AND PRH.SpouseID <> 0))
	WHERE PRH.Housing IS NULL


	
	
	-- Insert Student Dorm assignments into the system
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ST_HOUSING"'
			+ ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
			+ ',"Entity2ID":"' + CAST(HD.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN SS.Term = 1 THEN ',"StartDate":"' 
					+ '08/10/' + CAST(SS.Year AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(SS.Year AS nvarchar) + '"'
				ELSE CASE WHEN SS.Term = 2 THEN ',"StartDate":"' 
					+ '12/16/' + CAST(SS.Year AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(SS.Year+1 AS nvarchar) + '"'
				ELSE ',"StartDate":"' 
					+ '04/11/' + CAST(SS.Year+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(SS.Year+1 AS nvarchar) + '"'
				END END
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT *
	FROM OpenQUERY(STUDData97,'SELECT * FROM [Enrollment-Term] WHERE 
									[Type of Term] = ''Term'' AND [Term] > 0 AND NOT [Housing] IS NULL') SS
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
		ON P.PersonID = SS.StudentID
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {HousingDIV.Dorm}''') AS HD
		ON HD.DormName = SS.Housing
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ST_Housing}''') AS STH
		ON STH.Entity1ID = P.EntityID AND STH.Entity2ID = HD.EntityID
			AND STH.StartDate = CASE WHEN SS.Term = 1 THEN CAST('08/10/' + CAST(SS.Year AS nvarchar) AS DateTime)
						ELSE CASE WHEN SS.Term = 2 THEN CAST('12/16/' + CAST(SS.Year AS nvarchar) AS DateTime)
												  ELSE CAST('04/11/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
						END END
			AND STH.EndDate = CASE WHEN SS.Term = 1 THEN CAST('12/15/' + CAST(SS.Year AS nvarchar) AS DateTime)
						ELSE CASE WHEN SS.Term = 2 THEN CAST('04/10/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
												  ELSE CAST('07/25/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
						END END
	WHERE STH.Entity2ID IS NULL

	-- Delete invalid student dorm assignments from the system
	-- Insert Student Dorm assignments into the system
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True","ShortRelationshipName":"ST_HOUSING"'
			+ ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
			+ ',"Entity2ID":"' + CAST(HD.EntityID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(STH.StartDate AS NVARCHAR) + '"'
			+ ',"EndDate":"' + CAST(STH.EndDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT *
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ST_Housing}''') AS STH
		ON STH.Entity1ID = P.EntityID
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {HousingDIV.Dorm}''') AS HD
		ON HD.EntityID = STH.Entity2ID
	LEFT JOIN
	OpenQUERY(STUDData97,'SELECT * FROM [Enrollment-Term] WHERE 
									[Type of Term] = ''Term'' AND [Term] > 0 AND NOT [Housing] IS NULL') SS
		ON SS.StudentID = P.PersonID AND HD.DormName = SS.Housing 
			AND STH.StartDate = CASE WHEN SS.Term = 1 THEN CAST('08/10/' + CAST(SS.Year AS nvarchar) AS DateTime)
						ELSE CASE WHEN SS.Term = 2 THEN CAST('12/16/' + CAST(SS.Year AS nvarchar) AS DateTime)
												  ELSE CAST('04/11/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
						END END
			AND STH.EndDate = CASE WHEN SS.Term = 1 THEN CAST('12/15/' + CAST(SS.Year AS nvarchar) AS DateTime)
						ELSE CASE WHEN SS.Term = 2 THEN CAST('04/10/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
												  ELSE CAST('07/25/' + CAST(SS.Year+1 AS nvarchar) AS DateTime)
						END END
	WHERE SS.StudentID IS NULL
		

	



	RETURN 
END